package com.siemens.industrial.web.mvc.dao.partner;

import com.siemens.industrial.model.partner.ProductInfo;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/11/8.
 */
@Service
public class PartnerProductInfoDao {

    @Resource
    JdbcTemplate jdbcTemplate;


    private static final String databaseTag = "{database}";
    private static final String updateByDeviceId = "UPDATE `" + databaseTag + "`.`product_info` SET `user_id` = ?, `product_num` = ?, `good_prod_num` = ?,`bad_prod_num` = ?,`prod_rate`=?,`shift_lead`=?  WHERE `device_id` = ? and `start_time` = ?";
    private static final String saveProductQuery = "INSERT INTO `" + databaseTag + "`.`product_info` (`device_id`, `start_time`, `user_id`, `product_num`, `good_prod_num`, `bad_prod_num`) VALUES (?, ?, ?, ?, ?, ?);";
    private static final String findOneByDeviceId = "select * from `" + databaseTag + "`.`product_info` where device_id = ? and start_time = ?";
    private static final String findStartTimeWhenInsert = "SELECT DISTINCT DATE(t.start_time)\n" +
            "FROM\n" +
            "    `" + databaseTag + "`.device_state_period t\n" +
            "left join `" + databaseTag + "`.product_info p\n" +
            "on p.device_id = t.device_id\n" +
            "and date(p.start_time) = date(t.start_time)\n" +
            "where p.device_id is null and t.device_id=?";
    private static final String findOneHaveUniqueId = "SELECT \n" +
            "    a.device_id deviceId,\n" +
            "    DATE_FORMAT(a.start_time, '%Y-%m-%d') startTime,\n" +
            "    a.product_num productNum,\n" +
            "    a.good_prod_num goodNum,\n" +
            "    a.bad_prod_num badNum,\n" +
            "    b.device_unique_id uniqueId\n" +
            "FROM\n" +
            "    `" + databaseTag + "`.product_info a\n" +
            "        INNER JOIN\n" +
            "    `" + databaseTag + "`.device b ON a.device_id = b.device_id\n" +
            "WHERE\n" +
            "    a.device_id = ?\n" +
            "        AND a.start_time = ?";
    private static final String findAllOrderByStartTime = "SELECT \n" +
            "    a.device_id deviceId,\n" +
            "    date_format(a.start_time,'%Y-%m-%d') startTime,\n" +
            "    a.end_time endTime,\n" +
            "    a.product_num produtNum,\n" +
            "    a.good_prod_num goodNum,\n" +
            "    a.bad_prod_num badNum,\n" +
            "    b.user_name userName,\n" +
            "    c.device_unique_id uniqueId\n" +
            "FROM\n" +
            "    `" + databaseTag + "`.product_info a\n" +
            "        INNER JOIN\n" +
            "    `" + databaseTag + "`.`user` b ON a.user_id = b.user_id\n" +
            "        INNER JOIN\n" +
            "    `" + databaseTag + "`.device c ON a.device_id = c.device_id\n" +
            "WHERE\n" +
            "    a.device_id = ?\n" +
            "ORDER BY a.start_time\n" +
            "LIMIT 0 , 10;";
    private static final String findServicesByTime = "SELECT \n" +
            "    s.date,\n" +
            "    IFNULL(a.oee, 0) oee,\n" +
            "    IFNULL(a.availability, 0) availability,\n" +
            "    IFNULL(a.quality, 0) quality,\n" +
            "    IFNULL(a.performance, 0) performance\n" +
            "FROM\n" +
            "    (SELECT DISTINCT\n" +
            "        (ADDDATE(?, numlist.id)) AS `date`\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        n1.i + n10.i * 10 + n100.i * 100 AS id\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.`num` n1\n" +
            "    CROSS JOIN `" + databaseTag + "`.`num` AS n10\n" +
            "    CROSS JOIN `" + databaseTag + "`.`num` AS n100) AS numlist\n" +
            "    WHERE\n" +
            "        ADDDATE(?, numlist.id) <= ?) s\n" +
            "        LEFT JOIN\n" +
            "    (SELECT \n" +
            "        *\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.device_kpi\n" +
            "    WHERE\n" +
            "        (date_id BETWEEN ? AND ?)\n" +
            "            AND device_id = ?) a ON s.`date` = a.date_id order by s.date;";

    private static final String findAWeekTime = "SELECT \n" +
            "    s.date\n" +
            "FROM\n" +
            "    (SELECT DISTINCT\n" +
            "        (ADDDATE(?, numlist.id)) AS `date`\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        n1.i + n10.i * 10 + n100.i * 100 AS id\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.`num` n1\n" +
            "    CROSS JOIN `" + databaseTag + "`.`num` AS n10\n" +
            "    CROSS JOIN `" + databaseTag + "`.`num` AS n100) AS numlist\n" +
            "    WHERE\n" +
            "        ADDDATE(?, numlist.id) <= ?) s\n";
    private static final String insertProductInfo = "insert into `" + databaseTag + "`.`product_info` (device_id,start_time,end_time,user_id,product_num,good_prod_num,bad_prod_num,prod_rate,shift_lead,work_diff_time,stop_diff_time)\n" +
            "select ? device_id, \n" +
            "date_add(?, interval ? hour_second) start_time, \n" +
            "date_add(?, interval ? hour_second) end_time, \n" +
            "? user_id,\n" +
            "? product_num, \n" +
            "? good_prod_num, \n" +
            "? bad_prod_num, \n" +
            "? prod_rate,\n" +
            "? shift_lead,\n" +
            "ifnull((select sum(timestampdiff(second, \n" +
            " if(start_time < date_add(?, interval ? hour_second),date_add(?, interval ? hour_second),start_time),\n" +
            " if(end_time > date_add(?, interval ? hour_second), date_add(?, interval ? hour_second), end_time))\n" +
            " ) work_diff_time\n" +
            " from `" + databaseTag + "`.`device_state_period`\n" +
            " where start_time < date_add(?, interval ? hour_second)\n" +
            " and end_time > date_add(?, interval ? hour_second)\n" +
            " and state = 1\n" +
            " and device_id = ?), 0) work_diff_time,\n" +
            "ifnull((select sum(timestampdiff(second, \n" +
            " if(start_time < date_add(?, interval ? hour_second),date_add(?, interval ? hour_second),start_time),\n" +
            " if(end_time > date_add(?, interval ? hour_second),date_add(?, interval ? hour_second), end_time))\n" +
            " ) stop_diff_time\n" +
            " from (select device_id, start_time, \n" +
            "  end_time\n" +
            " from `" + databaseTag + "`.`device_state_period`\n" +
            " where state = -1\n" +
            " and device_id=?) a\n" +
            " where start_time < date_add(?, interval ? hour_second)\n" +
            " and end_time > date_add(?, interval ? hour_second)), 0) stop_diff_time";
    private static final String findDeviceState = "select \n" +
            "sum(timestampdiff(second, \n" +
            "if(start_time < ?,?,start_time),\n" +
            "if(end_time > ?, ?, end_time))\n" +
            ") work_diff_time\n" +
            "from `" + databaseTag + "`.`device_state_period` \n" +
            "where start_time < ?\n" +
            "and end_time > ?\n" +
            "and state = 1\n" +
            "and device_id = ?\n" +
            "group by device_id";
    private static final String findProductByDay = "SELECT \n" +
            "    a.date_id date,\n" +
            "    CASE\n" +
            "        WHEN a.prod_num <> b.shift_num THEN 'part'\n" +
            "        WHEN a.prod_num = b.shift_num THEN 'all'\n" +
            "    END state,\n" +
            "    b.device_id deviceId\n" +
            "FROM\n" +
            "    (SELECT \n" +
            "        device_id, DATE(start_time) date_id, COUNT(1) prod_num\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.`product_info` p\n" +
            "    WHERE\n" +
            "        DATE(p.start_time) BETWEEN ? AND ?\n" +
            "and ((work_diff_time <> 0 and product_num <> 0) or (work_diff_time = 0))" +
            "    GROUP BY device_id , DATE(start_time)) a\n" +
            "        LEFT JOIN\n" +
            "    (SELECT \n" +
            "        device_id, COUNT(1) shift_num, begin_date, end_date\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.`device_shift_relation`\n" +
            "    WHERE\n" +
            "        ((begin_date <= ?\n" +
            "            AND end_date >= ?)\n" +
            "            OR (begin_date <= ?\n" +
            "            AND end_date >= ?)\n" +
            "            OR (begin_date >= ?\n" +
            "            AND end_date <= ?))\n" +
            "            AND plan_state = 1\n" +
            "    GROUP BY device_id , begin_date , end_date) b ON a.device_id = b.device_id\n" +
            "        AND a.date_id >= b.begin_date\n" +
            "        AND a.date_id <= b.end_date;";
    //    private static final String findShiftByDeviceIdAndTime = "SELECT \n" +
//            " a.device_id,\n" +
//            "    a.start_time,\n" +
//            "    a.end_time,\n" +
//            "    b.product_num,\n" +
//            "    b.good_prod_num,\n" +
//            "    b.bad_prod_num,\n" +
//            "    b.prod_rate,\n" +
//            "    sum(case when c.start_time <= a.start_datetime and c.end_time > a.start_datetime then 1 \n" +
//            " when c.start_time < a.end_datetime and c.end_time >= a.end_datetime then 1\n" +
//            "        when c.start_time >= a.start_datetime and c.end_time <= a.end_datetime then 1 \n" +
//            "        else 0 end) state\n" +
//            "FROM\n" +
//            "    (SELECT  device_id, start_time, end_time,\n" +
//            " STR_TO_DATE(concat(?,' ',start_time), '%Y-%m-%d %H:%i:%s') start_datetime, \n" +
//            "            STR_TO_DATE(concat(?,' ',end_time), '%Y-%m-%d %H:%i:%s') end_datetime\n" +
//            "       FROM `"+databaseTag+"`.`device_shift_relation`\n" +
//            "    WHERE begin_date <= ?\n" +
//            "    and end_date >= ?\n" +
//            "    AND device_id = ?\n" +
//            "    AND plan_state = 1) a\n" +
//            "left JOIN\n" +
//            "    (SELECT *\n" +
//            "    FROM `"+databaseTag+"`.`product_info`\n" +
//            "    WHERE device_id = ?\n" +
//            "     AND DATE(start_time) = ?) b \n" +
//            "ON a.start_datetime = b.start_time\n" +
//            "and a.device_id = b.device_id\n" +
//            "left join (select device_id, start_time, end_time\n" +
//            " from `"+databaseTag+"`.`device_state_period` \n" +
//            "            where state = 1 \n" +
//            "            and start_time <= ?\n" +
//            "            and end_time > ?\n" +
//            "            and device_id = ?) c\n" +
//            "on a.device_id = c.device_id\n" +
//            "group by a.device_id,\n" +
//            "    a.start_time,\n" +
//            "    a.end_time,\n" +
//            "    b.product_num,\n" +
//            "    b.good_prod_num,\n" +
//            "    b.bad_prod_num,\n" +
//            "    b.prod_rate";
    private static final String findShiftByDeviceIdAndTime = "SELECT \n" +
            "    c.device_id,\n" +
            "    c.start_time,\n" +
            "    c.end_time,\n" +
            "    c.product_num,\n" +
            "    c.good_prod_num,\n" +
            "    c.bad_prod_num,\n" +
            "    c.prod_rate,\n" +
            "    c.work_diff_time\n," +
            "    c.shift_name,\n" +
            "    c.shift_lead,\n" +
            "    CONCAT(FLOOR(LTRIM(c.work_diff_time / 3600)),\n" +
            "            '小时',\n" +
            "            FLOOR(LTRIM(c.work_diff_time % 3600 / 60)),\n" +
            "            '分钟',\n" +
            "            FLOOR(LTRIM(c.work_diff_time % 60)),\n" +
            "            '秒') time\n" +
            "FROM" +
            "(SELECT \n" +
            " a.device_id,\n" +
            "    a.start_time,\n" +
            "    a.end_time,\n" +
            "    a.shift_name,\n" +
            "    ifnull(b.shift_lead,a.shift_lead) shift_lead,\n" +
            "    ifnull(b.product_num,0) product_num,\n" +
            "    ifnull(b.good_prod_num,0) good_prod_num,\n" +
            "    ifnull(b.bad_prod_num,0) bad_prod_num,\n" +
            "    ifnull(b.prod_rate,0) prod_rate,\n" +
            " ifnull((select sum(timestampdiff(second, \n" +
            " if(start_time < a.start_datetime,a.start_datetime,start_time),\n" +
            " if(end_time > a.end_datetime, a.end_datetime, end_time))\n" +
            " ) work_diff_time\n" +
            " from `" + databaseTag + "`.`device_state_period` \n" +
            " where start_time < a.end_datetime\n" +
            " and end_time > a.start_datetime\n" +
            " and state = 1\n" +
            " and device_id = a.device_id), 0) work_diff_time\n" +
            "FROM\n" +
            "    (SELECT  device_id, start_time, end_time,shift_name,shift_lead,\n" +
            " date_add(str_to_date(?, '%Y-%m-%d'), interval start_time hour_second) start_datetime,\n" +
            " date_add(str_to_date(?, '%Y-%m-%d'), interval end_time hour_second) end_datetime\n" +
            "       FROM `" + databaseTag + "`.`device_shift_relation`\n" +
            "    WHERE begin_date <= ?\n" +
            "    and end_date >= ?\n" +
            "    AND device_id = ?\n" +
            "    AND plan_state = 1) a\n" +
            "left JOIN\n" +
            "    (SELECT *\n" +
            "    FROM `" + databaseTag + "`.`product_info`\n" +
            "    WHERE device_id = ?\n" +
            "     AND DATE(start_time) = ?) b \n" +
            "ON a.start_datetime = b.start_time\n" +
            "and a.device_id = b.device_id) c";

    @Transactional
    public void save(String database, ProductInfo productInfo) {
        jdbcTemplate.update(saveProductQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, productInfo.getDeviceId());
                    pss.setTimestamp(2, new java.sql.Timestamp(productInfo.getStartTime().getTime()));
//                    pss.setTimestamp(3, new java.sql.Timestamp(productInfo.getEndTime().getTime()));
                    pss.setString(3, productInfo.getUserId());
                    pss.setInt(4, productInfo.getProductNum());
                    pss.setInt(6, productInfo.getBadNum());
                    pss.setInt(5, productInfo.getGoodNum());
                }
        );
        database = "`" + database + "`";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String startTime = sdf.format(productInfo.getStartTime());
        String sql = "call " + database + "p_device_kpi(date('" + startTime + "'),'" + productInfo.getDeviceId() + "')";
        System.out.print(sql);
        jdbcTemplate.execute(sql);
    }

    public int update(String database, String deviceId, String startTime, String userId, int productNum, int goodNum, int badNum, double rate,String lead) {
        return jdbcTemplate.update(updateByDeviceId.replace(databaseTag, database), pss -> {
                    pss.setString(1, userId);
                    pss.setInt(2, productNum);
                    pss.setInt(3, goodNum);
                    pss.setInt(4, badNum);
                    pss.setDouble(5, rate);
                    pss.setString(6,lead);
                    pss.setString(7, deviceId);
                    pss.setString(8, startTime);
                }
        );
    }

    public List<Map<String, Object>> getAll(String database, String deviceId) {
        return jdbcTemplate.queryForList(findAllOrderByStartTime.replace(databaseTag, database), deviceId);
    }

    public Map<String, Object> getOneHaveUniqueId(String database, String deviceId, String startTime) {
        try {
            return jdbcTemplate.queryForList(findOneHaveUniqueId.replace(databaseTag, database), deviceId, startTime).get(0);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public ProductInfo getOne(String database, String deviceId, String startTime) {
        try {
            return jdbcTemplate.queryForObject(findOneByDeviceId.replace(databaseTag, database), ProductInfo.getRowMapper(), deviceId, startTime);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<Map<String, Object>> findStartTimes(String database, String deviceId) {
        return jdbcTemplate.queryForList(findAllOrderByStartTime.replace(databaseTag, database), deviceId);
    }

    public List<Map<String, Object>> findServicesByTime(String database, String startTime, String endTime, String deviceId) {
        List list = jdbcTemplate.queryForList(findServicesByTime.replace(databaseTag, database), startTime, startTime, endTime, startTime, endTime, deviceId);
        return list;
    }

    public List<String> findAWeekTime(String database, String startTime, String endTime) {
        return jdbcTemplate.queryForList(findAWeekTime.replace(databaseTag, database), String.class, startTime, startTime, endTime);
    }

    public int insertProductInfo(String database, String deviceId, String time, String startTime, String endTime, String userId, int productNum, int goodNum, int badNum, double rate, String lead) {
        int i = jdbcTemplate.update(insertProductInfo.replace(databaseTag, database), pss -> {
            pss.setString(1, deviceId);
            pss.setString(2, time);
            pss.setString(3, startTime);
            pss.setString(4, time);
            pss.setString(5, endTime);
            pss.setString(6, userId);
            pss.setInt(7, productNum);
            pss.setInt(8, goodNum);
            pss.setInt(9, badNum);
            pss.setDouble(10, rate);
            pss.setString(11,lead);
            pss.setString(12, time);
            pss.setString(13, startTime);
            pss.setString(14, time);
            pss.setString(15, startTime);
            pss.setString(16, time);
            pss.setString(17, endTime);
            pss.setString(18, time);
            pss.setString(19, endTime);
            pss.setString(20, time);
            pss.setString(21, endTime);
            pss.setString(22, time);
            pss.setString(23, startTime);
            pss.setString(24, deviceId);
            pss.setString(25, time);
            pss.setString(26, startTime);
            pss.setString(27, time);
            pss.setString(28, startTime);
            pss.setString(29, time);
            pss.setString(30, endTime);
            pss.setString(31, time);
            pss.setString(32, endTime);
            pss.setString(33, deviceId);
            pss.setString(34, time);
            pss.setString(35, endTime);
            pss.setString(36, time);
            pss.setString(37, startTime);
        });

        return i;
    }

    public void executeStoredProcedure(String database, String deviceId, String time) {
        database = "`" + database + "`";
        String sql = "call " + database + ".p_device_kpi(date('" + time + "'),'" + deviceId + "')";
        System.out.print(sql);
        jdbcTemplate.execute(sql);
    }

    public boolean findDeviceState(String database, String startTime, String endTime, String deviceId) {
        List<Long> list = jdbcTemplate.queryForList(findDeviceState.replace(databaseTag, database), Long.class, startTime, startTime, endTime, endTime, endTime, startTime, deviceId);
        if (list.size() == 1 && list.get(0) > 0) {
            return true;
        }
        return false;
    }

    public List<Map<String, Object>> findProductByDay(String database, String startTime, String endTime) {
        return jdbcTemplate.queryForList(findProductByDay.replace(databaseTag, database), startTime, endTime, startTime, startTime, endTime, endTime, startTime, endTime);
    }

    public List<Map<String, Object>> findShiftByDeviceIdAndTime(String database, String time, String deviceId) {
        return jdbcTemplate.queryForList(findShiftByDeviceIdAndTime.replace(databaseTag, database), time, time, time, time, deviceId, deviceId, time);
    }
}
